IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'View_OrdersWageDigital') BEGIN DROP VIEW [dbo].View_OrdersWageDigital END GO create view View_OrdersWageDigital as select tb_ErpOrderDigital.Id , Ordv_Number ,Ordv_ViceNumber ,Ordv_DigitalNumber ,Ord_Number ,Ord_Type ,Ord_Class ,Ord_PhotographyCategory ,Ord_SeriesName ,Ord_SeriesPrice ,(case when Ord_Type = '1' then (select Ordpg_Sights from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber) else (select top 1 Ordpg_Sights from tb_ErpOrdersPhotography where Ordpg_Number = Ord_Number) end) as 拍摄名称 ,(select Cus_Name from tb_ErpCustomer where Ord_MainContact=Cus_CustomerNumber) as 客户名称 ,(select Cus_Telephone from tb_ErpCustomer where Ord_MainContact=Cus_CustomerNumber) as 客户电话 ,Ordv_ClothingName as 礼服师ID ,dbo.fn_CheckUserIDGetUserName(Ordv_ClothingName) as 礼服师 ,Ordv_ClothingStatus as 礼服师状态 ,Ordv_ClothingTime as 选衣时间 , Ordv_FilmSelectionName as 选片师ID , dbo.fn_CheckUserIDGetUserName(Ordv_FilmSelectionName) as 选片师 , Ordv_FilmSelectionStatus as 选片状态 , Ordv_FilmSelectionTime as 选片时间 , Ordv_EarlyRepairName as 初修师ID , dbo.fn_CheckUserIDGetUserName(Ordv_EarlyRepairName) as 初修师 , Ordv_EarlyRepairStatus as 初修状态 , Ordv_EarlyRepairTime as 初修时间 , Ordv_RefinementName as 精修师ID , dbo.fn_CheckUserIDGetUserName(Ordv_RefinementName)as 精修师 , Ordv_RefinementStatus as 精修状态 , Ordv_RefinementTime as 精修时间 ,Ordv_LookDesignName as 看设计师ID ,dbo.fn_CheckUserIDGetUserName(Ordv_LookDesignName) as 看设计师 ,Ordv_LookDesignStatus as 看设计状态 ,Ordv_LookDesignTime as 看设计时间 ,Ordv_DesignerName as 设计师ID ,dbo.fn_CheckUserIDGetUserName(Ordv_DesignerName) as 设计师 ,Ordv_DesignerStatus as 设计状态 , Ordv_DesignerTime as 设计时间 ,(case when (select Count(*) from (select [OPlist_PickupStatus] from [tb_ErpOrderProductList] where [OPlist_ViceNumber]=Ordv_ViceNumber and OPlist_Type = '2' and [OPlist_PickupStatus] = '0') as ta)>0 then '未取' else 'OK' end) AS 取件状态 ,(select top 1 OPlist_PickupTime from tb_ErpOrderProductList where OPlist_ViceNumber = Ordv_ViceNumber and OPlist_Type = '2' and OPlist_PickupTime is not null order by OPlist_PickupTime) AS 取件日期 from tb_ErpOrderDigital left join tb_ErpOrder on tb_ErpOrderDigital.Ordv_Number=tb_ErpOrder.Ord_Number GO IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'View_OrdersWagePhotography') BEGIN DROP VIEW [dbo].View_OrdersWagePhotography END GO create view View_OrdersWagePhotography as SELECT tb_ErpOrdersPhotography.ID ,Ordpg_Number ,Ordpg_ViceNumber ,Ord_Type ,Ord_Class ,Ord_PhotographyCategory ,Ord_SeriesName ,Ord_SeriesPrice ,(select Cus_Name from tb_ErpCustomer where Ord_MainContact=Cus_CustomerNumber) as 客户名称 ,(select Cus_Telephone from tb_ErpCustomer where Ord_MainContact=Cus_CustomerNumber) as 客户电话 ,Ordpg_Sights as 拍摄名称 --,Ordpg_SightsLevel --,[dbo].[fn_GetClassCodeToName](Ordpg_SightsLevel,Ordpg_SightsLevel) as Sc_ClassName ,Ordpg_SightsType as 景点类别 ,Ordpg_ApparelQuantity as 服装套数 ,Ordpg_Photographer as 摄影师ID ,[dbo].[fn_CheckUserIDGetUserName](Ordpg_Photographer) as 摄影师名称 ,Ordpg_PhotographyTime as 拍摄时间 ,Ordpg_PhotographyStatus as 拍摄状态 --,[dbo].fn_CheckOrderPhotographyStatus(Ordpg_PhotographyStatus) as OrdPg_PhotographyStatusName ,Ordpg_PhotographyAssistant as 摄影助理ID ,[dbo].[fn_CheckUserIDGetUserName](Ordpg_PhotographyAssistant) as 摄影助理名称 ,Ordpg_MakeupArtist as 化妆师ID ,[dbo].[fn_CheckUserIDGetUserName](Ordpg_MakeupArtist) as 化妆师名称 ,Ordpg_MakeupAssistant as 化妆助理ID ,[dbo].[fn_CheckUserIDGetUserName](Ordpg_MakeupAssistant) as 化妆助理名称 ,Ordpg_BootDivision as 引导师ID ,[dbo].[fn_CheckUserIDGetUserName](Ordpg_BootDivision) as 引导师名称 --,Ordpg_ReservationPhotographyName --,Ordpg_ReservationPhotographyTime --,Ordpg_ReservationPhotographyAssistant --,Ordpg_ReservationMakeupArtist --,Ordpg_ReservationMakeupAssistant --,Ordpg_ReservationBootDivision FROM tb_ErpOrdersPhotography left join tb_ErpOrder on tb_ErpOrdersPhotography.Ordpg_Number=tb_ErpOrder.Ord_Number GO IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'View_ErpSatisfactionReturningMattersSet') BEGIN DROP VIEW [dbo].View_ErpSatisfactionReturningMattersSet END GO create view View_ErpSatisfactionReturningMattersSet as SELECT tb_ErpSatisfactionReturningMattersSet.ID ,Sfrms_SurveySetID ,Sfrms_Contents ,Sfrms_EnableStatus ,Sfrms_CreateTime ,Sfrms_CreateName ,Sfrms_UpdateTime ,Sfrms_UpdateName ,Sfss_Name ,(CASE sfrms_EnableStatus WHEN '0' THEN '启用' ELSE '不启用' END ) as sfrms_EnableStatusName FROM tb_ErpSatisfactionReturningMattersSet left join tb_ErpSatisfactionSurveySet on tb_ErpSatisfactionReturningMattersSet.Sfrms_SurveySetID=tb_ErpSatisfactionSurveySet.ID GO IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'View_ErpSatisfactionSurvey') BEGIN DROP VIEW [dbo].View_ErpSatisfactionSurvey END GO create view View_ErpSatisfactionSurvey as SELECT tb_ErpSatisfactionSurvey.ID ,Sfs_OrderNumber ,Sfs_OrderNumberDeputy ,Sfs_EmployeeID ,(select [User_Name] from tb_ErpUser where Sfs_EmployeeID=User_EmployeeID) as Sfs_EmployeeName ,Sfs_Type ,Sfs_SurveySetID ,Sfss_Name as Sfs_SurveySetName ,Sfs_ReturningMattersSetID ,Sfrms_Contents ,Sfs_RatingSetID ,Sfrs_Name as Sfs_RatingSetName ,Sfs_Remark ,Sfs_CreateName as Sfs_CreateNameID ,(select [User_Name] from tb_ErpUser where Sfs_CreateName=User_EmployeeID) as Sfs_CreateName ,Sfs_CreateTime ,Sfs_UpdateTime ,Sfs_UpdateName as Sfs_UpdateNameID ,(select [User_Name] from tb_ErpUser where Sfs_UpdateName=User_EmployeeID) as Sfs_UpdateName ,Ord_CustomerName1 ,Ord_CustomerSex1 ,Ord_CustomerTelephone1 ,Sfrs_Scores ,(select count(id) from tb_ErpSatisfactionReturningMattersSet where Sfrms_SurveySetID=Sfs_SurveySetID) as SatisfactionReturningMattersSetCount FROM tb_ErpSatisfactionSurvey left join tb_ErpSatisfactionSurveySet on Sfs_SurveySetID=tb_ErpSatisfactionSurveySet.ID left join tb_ErpSatisfactionReturningMattersSet on Sfs_ReturningMattersSetID=tb_ErpSatisfactionReturningMattersSet.ID left join tb_ErpSatisfactionRatingSet on Sfs_RatingSetID=tb_ErpSatisfactionRatingSet.ID left join View_Customer_PaymentOrdersTwo on Sfs_OrderNumber=Ord_Number GO IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'View_OtherCustomers') BEGIN DROP VIEW [dbo].View_OtherCustomers END GO Create View View_OtherCustomers as SELECT distinct Tsorder_CustomerNumber , [Tsorder_CustomerName] ,[Tsorder_Telephone] ,Cus_NamePinyin FROM Vw_TwoSalesOrder GO IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'View_Customer_PaymentOrdersTwo') BEGIN DROP VIEW [dbo].View_Customer_PaymentOrdersTwo END GO Create view [dbo].[View_Customer_PaymentOrdersTwo] as select ID, Ord_Number, Ord_DividedShop, Ord_Type, Ord_SeriesName, Ord_SeriesPrice, Ord_Class, Ord_CustomerListID, Ord_MainContact, (select Cus_Name from tb_ErpCustomer where Ord_MainContact=Cus_CustomerNumber) as Ord_CustomerName1, (select Cus_Sex from tb_ErpCustomer where Ord_MainContact=Cus_CustomerNumber) as Ord_CustomerSex1, (select Cus_Telephone from tb_ErpCustomer where Ord_MainContact=Cus_CustomerNumber) as Ord_CustomerTelephone1, (CASE Ord_Type WHEN '0' THEN ( select substring(Ord_CustomerListID,charindex(',',Ord_CustomerListID+',')+1,charindex(',',Ord_CustomerListID+',')-1)) WHEN '1' THEN ( select substring(Ord_CustomerListID,charindex(',',Ord_CustomerListID+',')+1,charindex(',',Ord_CustomerListID+',')-1)) ELSE '' END ) as Ord_CustomerListID2 , (select Cus_Name from tb_ErpCustomer where (CASE Ord_Type WHEN '0' THEN ( select substring(Ord_CustomerListID,charindex(',',Ord_CustomerListID+',')+1,charindex(',',Ord_CustomerListID+',')-1)) WHEN '1' THEN ( select substring(Ord_CustomerListID,charindex(',',Ord_CustomerListID+',')+1,charindex(',',Ord_CustomerListID+',')-1)) ELSE '' END )=Cus_CustomerNumber) as Ord_CustomerName2, (select Cus_Telephone from tb_ErpCustomer where (CASE Ord_Type WHEN '0' THEN ( select substring(Ord_CustomerListID,charindex(',',Ord_CustomerListID+',')+1,charindex(',',Ord_CustomerListID+',')-1)) WHEN '1' THEN ( select substring(Ord_CustomerListID,charindex(',',Ord_CustomerListID+',')+1,charindex(',',Ord_CustomerListID+',')-1)) ELSE '' END )=Cus_CustomerNumber) as Ord_CustomerTelephone2, (select stuff((select ','+dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('')),1,1,'')) as Ord_OrdersPerson, (select [Cus_NamePinyin] from tb_ErpCustomer where Ord_MainContact=Cus_CustomerNumber) AS name1, ((select [Cus_NamePinyin] from tb_ErpCustomer where (CASE Ord_Type WHEN '0' THEN ( select substring(Ord_CustomerListID,charindex(',',Ord_CustomerListID+',')+1,charindex(',',Ord_CustomerListID+',')-1)) WHEN '1' THEN ( select substring(Ord_CustomerListID,charindex(',',Ord_CustomerListID+',')+1,charindex(',',Ord_CustomerListID+',')-1)) ELSE '' END )=Cus_CustomerNumber)) AS name2, (select stuff((select ','+ OrdPe_OrdersPerson from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('')),1,1,'')) as Ord_OrdersPersonID, Ord_CreateDatetime ,( SELECT count(id) FROM tb_ErpOrderProductList where OPlist_OrderNumber=Ord_Number and OPlist_PickupStatus=0) as PickupStatusCount from tb_ErpOrder GO IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'View_ErpPieceCommissionRecords') BEGIN DROP VIEW [dbo].View_ErpPieceCommissionRecords END GO Create view [dbo].[View_ErpPieceCommissionRecords] as SELECT tb_ErpPieceCommissionRecords.ID ,Pcr_OrderNumber ,Pcr_DigitalDivision ,Pcr_Date ,Pcr_CompletionContents ,Pcr_Quantity ,Pcr_CreateTime ,Pcr_EntryPeople ,Pcr_Type ,(select [User_Name] from tb_ErpUser where Pcr_DigitalDivision=User_EmployeeID) as Pcr_DigitalDivisionName ,[dbo].[fn_GetClassCodeToName](Pcr_CompletionContents,Pcr_CompletionContents) as Pcr_CompletionContentsName ,(select [User_Name] from tb_ErpUser where Pcr_EntryPeople=User_EmployeeID) as Pcr_EntryPeopleName ,Ord_Type ,Ord_Class ,(select Cus_Name from tempTB_AggregationCustomer where Pcr_OrderNumber=GP_OrderNumber) as Cus_Name ,(select Cus_Telephone from tempTB_AggregationCustomer where Pcr_OrderNumber=GP_OrderNumber) as Cus_Telephone ,Ord_SeriesName ,Ord_SeriesPrice FROM tb_ErpPieceCommissionRecords left join tb_ErpOrder on tb_ErpPieceCommissionRecords.Pcr_OrderNumber=tb_ErpOrder.Ord_Number GO IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'View_OrdersWagePaymentRecords') BEGIN DROP VIEW [dbo].View_OrdersWagePaymentRecords END GO Create VIEW [dbo].[View_OrdersWagePaymentRecords] AS SELECT View_ErpPayment.ID ,Pay_OrdNumber ,Pay_AmountOf ,Pay_OpenSingle ,Pay_ThePayee ,Pay_PaymentMethod ,Pay_OrdersLocation ,Pay_ReceivableProject ,Pay_CustomNumber ,Pay_Remark ,convert(varchar(10),Pay_CreateDatetime,120) as Pay_CreateDatetime ,Pay_Category ,Pay_TwoPinsCategory ,Pay_UserName ,Pay_ThePayeeName ,Pay_PaymentMethodName ,Pay_TwoPinsCategoryName ,Pay_FinancialAuditdPeople ,Pay_FinancialAudit ,Pay_FinancialAuditdPeopleName ,Pay_ShootingName ,Ord_Number ,Ord_DividedShop ,Ord_Type ,Ord_Class , Ord_CustomerName1 ,name1 ,Ord_CustomerTelephone1 ,(select Tsorder_Name from tb_ErpTwoSalesOrder where Pay_OrdNumber= Tsorder_Number) as Tsorder_Name ,(select Cus_Name from View_DressSaleRentalOrder where Pay_OrdNumber=Dsro_Number) as Cus_Name ,(select Tsorder_CustomerName from View_TwoSalesOrder where Pay_OrdNumber= Tsorder_Number) as Tsorder_CustomerName ,Ord_CreateDatetime ,(select OrdPe_Type from tb_ErpOrdersPerson where OrdPe_OrderNumber=Pay_OrdNumber and OrdPe_OrdersPerson=Pay_OpenSingle) as OrdPe_Type ,Ord_SeriesName FROM View_ErpPayment left join View_Customer_PaymentOrdersTwo on View_ErpPayment.Pay_OrdNumber=View_Customer_PaymentOrdersTwo.Ord_Number GO IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'View_ErpSatisfactionSurvey') BEGIN DROP VIEW [dbo].View_ErpSatisfactionSurvey END GO Create view [dbo].[View_ErpSatisfactionSurvey] as SELECT tb_ErpSatisfactionSurvey.ID ,Sfs_OrderNumber ,Sfs_OrderNumberDeputy ,Sfs_EmployeeID ,(select User_Name from tb_ErpUser where Sfs_EmployeeID=User_EmployeeID) as Sfs_EmployeeName ,Sfs_Type ,Sfs_SurveySetID ,Sfss_Name as Sfs_SurveySetName ,Sfs_ReturningMattersSetID ,Sfrms_Contents ,Sfs_RatingSetID ,Sfrs_Name as Sfs_RatingSetName ,Sfs_Remark ,Sfs_CreateName as Sfs_CreateNameID ,(select User_Name from tb_ErpUser where Sfs_CreateName=User_EmployeeID) as Sfs_CreateName ,Sfs_CreateTime ,Sfs_UpdateTime ,Sfs_UpdateName as Sfs_UpdateNameID ,(select User_Name from tb_ErpUser where Sfs_UpdateName=User_EmployeeID) as Sfs_UpdateName , Ord_CustomerName1 ,name1 ,Ord_CustomerTelephone1 ,Sfrs_Scores ,(select count(id) from tb_ErpSatisfactionReturningMattersSet where Sfrms_SurveySetID=Sfs_SurveySetID) as SatisfactionReturningMattersSetCount FROM tb_ErpSatisfactionSurvey left join tb_ErpSatisfactionSurveySet on Sfs_SurveySetID=tb_ErpSatisfactionSurveySet.ID left join tb_ErpSatisfactionReturningMattersSet on Sfs_ReturningMattersSetID=tb_ErpSatisfactionReturningMattersSet.ID left join tb_ErpSatisfactionRatingSet on Sfs_RatingSetID=tb_ErpSatisfactionRatingSet.ID left join View_Customer_PaymentOrdersTwo on Sfs_OrderNumber=Ord_Number GO IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'View_OrdersWageDigital') BEGIN DROP VIEW [dbo].View_OrdersWageDigital END GO Create view [dbo].[View_OrdersWageDigital] as select tb_ErpOrderDigital.Id , Ordv_Number ,Ordv_ViceNumber ,Ordv_DigitalNumber ,Ord_Number ,Ord_Type ,Ord_Class ,Ord_PhotographyCategory ,Ord_SeriesName ,Ord_SeriesPrice ,(case when Ord_Type = '1' then (select Ordpg_Sights from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber) else (select top 1 Ordpg_Sights from tb_ErpOrdersPhotography where Ordpg_Number = Ord_Number) end) as 拍摄名称 ,(select Cus_Name from tempTB_AggregationCustomer where Ord_Number=GP_OrderNumber) as 客户名称 ,(select Cus_Telephone from tempTB_AggregationCustomer where Ord_Number=GP_OrderNumber) as 客户电话 ,Ordv_ClothingName as 礼服师ID ,dbo.fn_CheckUserIDGetUserName(Ordv_ClothingName) as 礼服师 ,Ordv_ClothingStatus as 礼服师状态 ,Ordv_ClothingTime as 选衣时间 , Ordv_FilmSelectionName as 选片师ID , dbo.fn_CheckUserIDGetUserName(Ordv_FilmSelectionName) as 选片师 , Ordv_FilmSelectionStatus as 选片状态 , Ordv_FilmSelectionTime as 选片时间 , Ordv_EarlyRepairName as 初修师ID , dbo.fn_CheckUserIDGetUserName(Ordv_EarlyRepairName) as 初修师 , Ordv_EarlyRepairStatus as 初修状态 , Ordv_EarlyRepairTime as 初修时间 , Ordv_RefinementName as 精修师ID , dbo.fn_CheckUserIDGetUserName(Ordv_RefinementName)as 精修师 , Ordv_RefinementStatus as 精修状态 , Ordv_RefinementTime as 精修时间 ,Ordv_LookDesignName as 看设计师ID ,dbo.fn_CheckUserIDGetUserName(Ordv_LookDesignName) as 看设计师 ,Ordv_LookDesignStatus as 看设计状态 ,Ordv_LookDesignTime as 看设计时间 ,Ordv_DesignerName as 设计师ID ,dbo.fn_CheckUserIDGetUserName(Ordv_DesignerName) as 设计师 ,Ordv_DesignerStatus as 设计状态 , Ordv_DesignerTime as 设计时间 ,(case when (select Count(*) from (select [OPlist_PickupStatus] from [tb_ErpOrderProductList] where [OPlist_ViceNumber]=Ordv_ViceNumber and OPlist_Type = '2' and [OPlist_PickupStatus] = '0') as ta)>0 then '未取' else 'OK' end) AS 取件状态 ,(select top 1 OPlist_PickupTime from tb_ErpOrderProductList where OPlist_ViceNumber = Ordv_ViceNumber and OPlist_Type = '2' and OPlist_PickupTime is not null order by OPlist_PickupTime) AS 取件日期 ,(case Ord_Type when 0 then (select count(id) as id from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number and (Ordpg_PhotographyStatus=0 or Ordpg_PhotographyStatus=2)) when 1 then (select count(id) as id from tb_ErpOrdersPhotography where Ordv_ViceNumber=Ordpg_ViceNumber and (Ordpg_PhotographyStatus=0 or Ordpg_PhotographyStatus=2)) when 2 then (select count(id) as id from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number and (Ordpg_PhotographyStatus=0 or Ordpg_PhotographyStatus=2)) else '' end) as 未拍个数 from tb_ErpOrderDigital left join tb_ErpOrder on tb_ErpOrderDigital.Ordv_Number=tb_ErpOrder.Ord_Number GO IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'View_OrdersWagePhotography') BEGIN DROP VIEW [dbo].View_OrdersWagePhotography END GO Create view [dbo].[View_OrdersWagePhotography] as SELECT tb_ErpOrdersPhotography.ID ,Ordpg_Number ,Ordpg_ViceNumber ,Ord_Type ,Ord_Class ,Ord_PhotographyCategory ,Ord_SeriesName ,Ord_SeriesPrice ,(select Cus_Name from tempTB_AggregationCustomer where Ord_Number=GP_OrderNumber) as 客户名称 ,(select Cus_Telephone from tempTB_AggregationCustomer where Ord_Number=GP_OrderNumber) as 客户电话 ,Ordpg_Sights as 拍摄名称 --,Ordpg_SightsLevel --,[dbo].[fn_GetClassCodeToName](Ordpg_SightsLevel,Ordpg_SightsLevel) as Sc_ClassName ,Ordpg_SightsType as 景点类别 ,Ordpg_ApparelQuantity as 服装套数 ,Ordpg_Photographer as 摄影师ID ,[dbo].[fn_CheckUserIDGetUserName](Ordpg_Photographer) as 摄影师名称 ,Ordpg_PhotographyTime as 拍摄时间 ,Ordpg_PhotographyStatus as 拍摄状态 --,[dbo].fn_CheckOrderPhotographyStatus(Ordpg_PhotographyStatus) as OrdPg_PhotographyStatusName ,Ordpg_PhotographyAssistant as 摄影助理ID ,[dbo].[fn_CheckUserIDGetUserName](Ordpg_PhotographyAssistant) as 摄影助理名称 ,Ordpg_MakeupArtist as 化妆师ID ,[dbo].[fn_CheckUserIDGetUserName](Ordpg_MakeupArtist) as 化妆师名称 ,Ordpg_MakeupAssistant as 化妆助理ID ,[dbo].[fn_CheckUserIDGetUserName](Ordpg_MakeupAssistant) as 化妆助理名称 ,Ordpg_BootDivision as 引导师ID ,[dbo].[fn_CheckUserIDGetUserName](Ordpg_BootDivision) as 引导师名称 --,Ordpg_ReservationPhotographyName --,Ordpg_ReservationPhotographyTime --,Ordpg_ReservationPhotographyAssistant --,Ordpg_ReservationMakeupArtist --,Ordpg_ReservationMakeupAssistant --,Ordpg_ReservationBootDivision FROM tb_ErpOrdersPhotography left join tb_ErpOrder on tb_ErpOrdersPhotography.Ordpg_Number=tb_ErpOrder.Ord_Number GO IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'Vw_Customer_PaymentOrders') BEGIN DROP VIEW [dbo].Vw_Customer_PaymentOrders END GO Create view [dbo].Vw_Customer_PaymentOrders as select tb_ErpOrder.ID ,Ord_Number ,Ord_DividedShop ,Ord_Type ,Ord_PhotographyCategory ,Ord_SeriesName ,Ord_SeriesPrice ,Ord_Class ,GP_OrderNumber ,GP_CustomerGroupID ,Cus_Name ,Cus_Name_py ,Cus_Telephone ,M_Cus_CustomerNumber ,(select stuff((select ','+ OrdPe_OrdersPerson from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('')),1,1,'')) as Ord_OrdersPersonID ,(select stuff((select ','+dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('')),1,1,'')) as Ord_OrdersPerson ,Ord_CreateDatetime ,( SELECT count(id) FROM tb_ErpOrderProductList where OPlist_OrderNumber=Ord_Number and OPlist_PickupStatus=0) as PickupStatusCount from tb_ErpOrder left join tempTB_AggregationCustomer on Ord_Number=GP_OrderNumber GO IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'Vw_OrdersWagePaymentRecords') BEGIN DROP VIEW [dbo].Vw_OrdersWagePaymentRecords END GO Create VIEW [dbo].Vw_OrdersWagePaymentRecords AS SELECT View_ErpPayment.ID ,Pay_OrdNumber ,Pay_AmountOf ,Pay_OpenSingle ,Pay_ThePayee ,Pay_PaymentMethod ,Pay_OrdersLocation ,Pay_ReceivableProject ,Pay_CustomNumber ,Pay_Remark ,convert(varchar(10),Pay_CreateDatetime,120) as Pay_CreateDatetime ,Pay_Category ,Pay_TwoPinsCategory ,Pay_UserName ,Pay_ThePayeeName ,Pay_PaymentMethodName ,Pay_TwoPinsCategoryName ,Pay_FinancialAuditdPeople ,Pay_FinancialAudit ,Pay_FinancialAuditdPeopleName ,Pay_ShootingName ,Ord_Number ,Ord_DividedShop ,Ord_Type ,Ord_Class ,Cus_Name as Ord_CustomerName1 ,Cus_Name_py ,Cus_Telephone ,(select Tsorder_Name from tb_ErpTwoSalesOrder where Pay_OrdNumber= Tsorder_Number) as Tsorder_Name ,(select Cus_Name from View_DressSaleRentalOrder where Pay_OrdNumber=Dsro_Number) as Cus_Name ,(select Tsorder_CustomerName from View_TwoSalesOrder where Pay_OrdNumber= Tsorder_Number) as Tsorder_CustomerName ,Ord_CreateDatetime ,(select OrdPe_Type from tb_ErpOrdersPerson where OrdPe_OrderNumber=Pay_OrdNumber and OrdPe_OrdersPerson=Pay_OpenSingle) as OrdPe_Type ,Ord_SeriesName ,Ord_PhotographyCategory FROM View_ErpPayment left join Vw_Customer_PaymentOrders on View_ErpPayment.Pay_OrdNumber=Vw_Customer_PaymentOrders.Ord_Number GO IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'Vw_ErpPieceCommissionRecords') BEGIN DROP VIEW [dbo].Vw_ErpPieceCommissionRecords END GO Create view [dbo].Vw_ErpPieceCommissionRecords as SELECT tb_ErpPieceCommissionRecords.ID ,Pcr_OrderNumber ,Pcr_DigitalDivision ,Pcr_Date ,Pcr_CompletionContents ,Pcr_Quantity ,Pcr_CreateTime ,Pcr_EntryPeople ,Pcr_Type ,(select [User_Name] from tb_ErpUser where Pcr_DigitalDivision=User_EmployeeID) as Pcr_DigitalDivisionName ,[dbo].[fn_GetClassCodeToName](Pcr_CompletionContents,Pcr_CompletionContents) as Pcr_CompletionContentsName ,(select [User_Name] from tb_ErpUser where Pcr_EntryPeople=User_EmployeeID) as Pcr_EntryPeopleName ,Ord_Type ,Ord_Class ,(select Cus_Name from tempTB_AggregationCustomer where Pcr_OrderNumber=GP_OrderNumber) as Cus_Name ,(select Cus_Telephone from tempTB_AggregationCustomer where Pcr_OrderNumber=GP_OrderNumber) as Cus_Telephone ,Ord_SeriesName ,Ord_SeriesPrice ,Pcr_Quantity*(select Wcs_Percentage from tb_ErpWageCommissionSet where Pcr_CompletionContents=Wcs_TypeCode) as 总价格 FROM tb_ErpPieceCommissionRecords left join tb_ErpOrder on tb_ErpPieceCommissionRecords.Pcr_OrderNumber=tb_ErpOrder.Ord_Number GO IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'Vw_ErpSatisfactionSurvey') BEGIN DROP VIEW [dbo].Vw_ErpSatisfactionSurvey END GO Create view [dbo].Vw_ErpSatisfactionSurvey as SELECT tb_ErpSatisfactionSurvey.ID ,Sfs_OrderNumber ,Sfs_OrderNumberDeputy ,Sfs_EmployeeID ,(select User_Name from tb_ErpUser where Sfs_EmployeeID=User_EmployeeID) as Sfs_EmployeeName ,Sfs_Type ,Sfs_SurveySetID ,Sfss_Name as Sfs_SurveySetName ,Sfs_ReturningMattersSetID ,Sfrms_Contents ,Sfs_RatingSetID ,Sfrs_Name as Sfs_RatingSetName ,Sfs_Remark ,Sfs_CreateName as Sfs_CreateNameID ,(select User_Name from tb_ErpUser where Sfs_CreateName=User_EmployeeID) as Sfs_CreateName ,Sfs_CreateTime ,Sfs_UpdateTime ,Sfs_UpdateName as Sfs_UpdateNameID ,(select User_Name from tb_ErpUser where Sfs_UpdateName=User_EmployeeID) as Sfs_UpdateName ,Cus_Name ,Cus_Name_py ,Cus_Telephone ,Sfrs_Scores ,(select count(id) from tb_ErpSatisfactionReturningMattersSet where Sfrms_SurveySetID=Sfs_SurveySetID) as SatisfactionReturningMattersSetCount FROM tb_ErpSatisfactionSurvey left join tb_ErpSatisfactionSurveySet on Sfs_SurveySetID=tb_ErpSatisfactionSurveySet.ID left join tb_ErpSatisfactionReturningMattersSet on Sfs_ReturningMattersSetID=tb_ErpSatisfactionReturningMattersSet.ID left join tb_ErpSatisfactionRatingSet on Sfs_RatingSetID=tb_ErpSatisfactionRatingSet.ID left join Vw_Customer_PaymentOrders on Sfs_OrderNumber=Ord_Number GO IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'Vw_OrdersWageDigital') BEGIN DROP VIEW [dbo].Vw_OrdersWageDigital END GO Create view [dbo].Vw_OrdersWageDigital as select tb_ErpOrderDigital.Id , Ordv_Number ,Ordv_ViceNumber ,Ordv_DigitalNumber ,Ord_Number ,Ord_Type ,Ord_Class ,Ord_PhotographyCategory ,Ord_SeriesName ,Ord_SeriesPrice ,(case when Ord_Type = '1' then (select Ordpg_Sights from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber) else (select top 1 Ordpg_Sights from tb_ErpOrdersPhotography where Ordpg_Number = Ord_Number) end) as 拍摄名称 ,(select Cus_Name from tempTB_AggregationCustomer where Ord_Number=GP_OrderNumber) as 客户名称 ,(select Cus_Telephone from tempTB_AggregationCustomer where Ord_Number=GP_OrderNumber) as 客户电话 ,Ordv_ClothingName as 礼服师ID ,dbo.fn_CheckUserIDGetUserName(Ordv_ClothingName) as 礼服师 ,Ordv_ClothingStatus as 礼服师状态 ,Ordv_ClothingTime as 选衣时间 , Ordv_FilmSelectionName as 选片师ID , dbo.fn_CheckUserIDGetUserName(Ordv_FilmSelectionName) as 选片师 , Ordv_FilmSelectionStatus as 选片状态 , Ordv_FilmSelectionTime as 选片时间 , Ordv_EarlyRepairName as 初修师ID , dbo.fn_CheckUserIDGetUserName(Ordv_EarlyRepairName) as 初修师 , Ordv_EarlyRepairStatus as 初修状态 , Ordv_EarlyRepairTime as 初修时间 , Ordv_RefinementName as 精修师ID , dbo.fn_CheckUserIDGetUserName(Ordv_RefinementName)as 精修师 , Ordv_RefinementStatus as 精修状态 , Ordv_RefinementTime as 精修时间 ,Ordv_LookDesignName as 看设计师ID ,dbo.fn_CheckUserIDGetUserName(Ordv_LookDesignName) as 看设计师 ,Ordv_LookDesignStatus as 看设计状态 ,Ordv_LookDesignTime as 看设计时间 ,Ordv_DesignerName as 设计师ID ,dbo.fn_CheckUserIDGetUserName(Ordv_DesignerName) as 设计师 ,Ordv_DesignerStatus as 设计状态 , Ordv_DesignerTime as 设计时间 ,(case when (select Count(*) from (select [OPlist_PickupStatus] from [tb_ErpOrderProductList] where [OPlist_ViceNumber]=Ordv_ViceNumber and OPlist_Type = '2' and [OPlist_PickupStatus] = '0') as ta)>0 then '未取' else 'OK' end) AS 取件状态 ,(select top 1 OPlist_PickupTime from tb_ErpOrderProductList where OPlist_ViceNumber = Ordv_ViceNumber and OPlist_Type = '2' and OPlist_PickupTime is not null order by OPlist_PickupTime) AS 取件日期 from tb_ErpOrderDigital left join tb_ErpOrder on tb_ErpOrderDigital.Ordv_Number=tb_ErpOrder.Ord_Number GO IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'Vw_OrdersWagePhotography') BEGIN DROP VIEW [dbo].Vw_OrdersWagePhotography END GO Create view [dbo].Vw_OrdersWagePhotography as SELECT tb_ErpOrdersPhotography.ID ,Ordpg_Number ,Ordpg_ViceNumber ,Ord_Type ,Ord_Class ,Ord_PhotographyCategory ,Ord_SeriesName ,Ord_SeriesPrice ,(select Cus_Name from tempTB_AggregationCustomer where Ord_Number=GP_OrderNumber) as 客户名称 ,(select Cus_Telephone from tempTB_AggregationCustomer where Ord_Number=GP_OrderNumber) as 客户电话 ,Ordpg_Sights as 拍摄名称 --,Ordpg_SightsLevel --,[dbo].[fn_GetClassCodeToName](Ordpg_SightsLevel,Ordpg_SightsLevel) as Sc_ClassName ,Ordpg_SightsType as 景点类别 ,Ordpg_ApparelQuantity as 服装套数 ,Ordpg_Photographer as 摄影师ID ,[dbo].[fn_CheckUserIDGetUserName](Ordpg_Photographer) as 摄影师名称 ,Ordpg_PhotographyTime as 拍摄时间 ,Ordpg_PhotographyStatus as 拍摄状态 --,[dbo].fn_CheckOrderPhotographyStatus(Ordpg_PhotographyStatus) as OrdPg_PhotographyStatusName ,Ordpg_PhotographyAssistant as 摄影助理ID ,[dbo].[fn_CheckUserIDGetUserName](Ordpg_PhotographyAssistant) as 摄影助理名称 ,Ordpg_MakeupArtist as 化妆师ID ,[dbo].[fn_CheckUserIDGetUserName](Ordpg_MakeupArtist) as 化妆师名称 ,Ordpg_MakeupAssistant as 化妆助理ID ,[dbo].[fn_CheckUserIDGetUserName](Ordpg_MakeupAssistant) as 化妆助理名称 ,Ordpg_BootDivision as 引导师ID ,[dbo].[fn_CheckUserIDGetUserName](Ordpg_BootDivision) as 引导师名称 --,Ordpg_ReservationPhotographyName --,Ordpg_ReservationPhotographyTime --,Ordpg_ReservationPhotographyAssistant --,Ordpg_ReservationMakeupArtist --,Ordpg_ReservationMakeupAssistant --,Ordpg_ReservationBootDivision FROM tb_ErpOrdersPhotography left join tb_ErpOrder on tb_ErpOrdersPhotography.Ordpg_Number=tb_ErpOrder.Ord_Number GO update tb_ErpSystemCategory set Sc_ClassName='提成点方案' where Sc_ClassCode='BEBBAFACDCCFBJFI' GO if not exists (select * from syscolumns where id=object_id('tb_ErpPayment') and name='Pay_Type') begin alter table tb_ErpPayment add Pay_Type int end GO if not exists (select * from syscolumns where id=object_id('tb_ErpTwoSalesOrder') and name='Tsorder_CustomerNumber') begin alter table tb_ErpTwoSalesOrder add Tsorder_CustomerNumber nvarchar(50) end GO IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'Vw_TwoSalesOrder') BEGIN DROP VIEW [dbo].Vw_TwoSalesOrder END GO Create view [dbo].Vw_TwoSalesOrder as SELECT dbo.tb_ErpTwoSalesOrder.ID, Tsorder_Number, Tsorder_Name, (select Cus_Name from tb_ErpCustomer where Tsorder_CustomerNumber=Cus_CustomerNumber) as Tsorder_CustomerName, (select Cus_Telephone from tb_ErpCustomer where Tsorder_CustomerNumber=Cus_CustomerNumber) as Tsorder_Telephone, (select Cus_NamePinyin from tb_ErpCustomer where Tsorder_CustomerNumber=Cus_CustomerNumber) as Cus_NamePinyin, Tsorder_Money, Tsorder_Quantity, Tsorder_Category, Tsorder_OpenSingle, Tsorder_PersonHandling, Tsorder_Remark, Tsorder_CreateDatetime, Tsorder_UpdateDatetime, dbo.fn_CheckUserIDGetUserName(Tsorder_OpenSingle) AS [User_Name], dbo.fn_CheckUserIDGetUserName(Tsorder_PersonHandling) AS Tsorder_PersonHandlingName, dbo.fn_GetClassCodeToName(Tsorder_Category,Tsorder_Category) AS Tsorder_CategoryName, Tsorder_CustomerNumber FROM dbo.tb_ErpTwoSalesOrder GO IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'Vw_OrdersWagePaymentRecords') BEGIN DROP VIEW [dbo].Vw_OrdersWagePaymentRecords END GO Create view [dbo].Vw_OrdersWagePaymentRecords as SELECT tb_ErpPayment.ID, Pay_OrdNumber, Pay_AmountOf, Pay_OpenSingle, Pay_ThePayee, Pay_PaymentMethod, Pay_OrdersLocation, Pay_ReceivableProject, Pay_Remark, convert(varchar(10),Pay_CreateDatetime,120) as Pay_CreateDatetime, Pay_Category, Pay_TwoPinsCategory, dbo.fn_CheckUserIDGetUserName(Pay_OpenSingle) AS Pay_UserName, dbo.fn_CheckUserIDGetUserName(Pay_ThePayee) AS Pay_ThePayeeName, dbo.fn_GetClassCodeToName(Pay_PaymentMethod, Pay_PaymentMethod) AS Pay_PaymentMethodName, dbo.fn_GetClassCodeToName(Pay_TwoPinsCategory, Pay_TwoPinsCategory) AS Pay_TwoPinsCategoryName, Pay_FinancialAuditdPeople, Pay_FinancialAudit, dbo.fn_CheckUserIDGetUserName(Pay_FinancialAuditdPeople)AS Pay_FinancialAuditdPeopleName, Pay_ShootingName, Pay_Type, Ord_DividedShop, Ord_Type, Cus_Name as Ord_CustomerName1, (select Tsorder_Name from Vw_TwoSalesOrder where Pay_OrdNumber= Tsorder_Number) as Tsorder_Name, (select Tsorder_CustomerName from Vw_TwoSalesOrder where Pay_OrdNumber= Tsorder_Number) as Tsorder_CustomerName, (select Cus_Name from View_DressSaleRentalOrder where Pay_OrdNumber=Dsro_Number) as Cus_Name, Ord_CreateDatetime, Ord_SeriesName, Ord_PhotographyCategory FROM tb_ErpPayment left join Vw_Customer_PaymentOrders on Pay_OrdNumber=Ord_Number GO IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'Vw_StaffPerformance_OrdersPerson') BEGIN DROP VIEW [dbo].Vw_StaffPerformance_OrdersPerson END GO create View Vw_StaffPerformance_OrdersPerson as SELECT ID ,Pay_OrdNumber as 订单号 ,Pay_ShootingName as 拍摄阶段 ,Pay_Category as 收款类别 ,Pay_TwoPinsCategory as 二销类别编号 ,dbo.fn_GetClassCodeToName(Pay_TwoPinsCategory, Pay_TwoPinsCategory) AS 二销类别名称 ,Pay_AmountOf as 收款金额 ,Pay_OpenSingle as 接单人编号 , dbo.fn_CheckUserIDGetUserName(Pay_OpenSingle) AS 接单人名称 ,Pay_ThePayee as 收款人编号 ,dbo.fn_CheckUserIDGetUserName(Pay_ThePayee) AS 收款人名称 ,Pay_PaymentMethod as 付款方式编号 ,dbo.fn_GetClassCodeToName(Pay_PaymentMethod, Pay_PaymentMethod) AS 付款方式名称 ,Pay_OrdersLocation as 接单地点 ,Pay_ReceivableProject as 收款项目 ,Pay_FinancialAudit as 审核状态 ,Pay_FinancialAuditdPeople as 审核人 ,Pay_Remark as 备注 ,Pay_CreateDatetime as 收款时间 ,Pay_Type as 收款类型 ,(case Pay_Type when 0 then (select Cus_Name from tempTB_AggregationCustomer where Pay_OrdNumber=GP_OrderNumber) when 1 then (select Tsorder_CustomerName from Vw_TwoSalesOrder where Pay_OrdNumber=Tsorder_Number) when 2 then (select Cus_Name from View_DressSaleRentalOrder where Pay_OrdNumber=Dsro_Number) else '' end) as '客户名称' ,(case Pay_Type when 0 then (select Ord_PhotographyCategory from tb_ErpOrder where Pay_OrdNumber=Ord_Number) else '' end) as '套系类别' ,(case Pay_Type when 0 then (select Ord_SeriesName from tb_ErpOrder where Pay_OrdNumber=Ord_Number) else '' end) as '套系名称' ,(case Pay_Type when 0 then (select Ord_SeriesPrice from tb_ErpOrder where Pay_OrdNumber=Ord_Number) when 1 then (select Tsorder_Money from Vw_TwoSalesOrder where Pay_OrdNumber=Tsorder_Number) when 2 then (select Dsro_Amount from View_DressSaleRentalOrder where Pay_OrdNumber=Dsro_Number) end) as '应收金额' ,(case Pay_Type when 0 then (dbo.fn_GetClassCodeToName(Pay_TwoPinsCategory, Pay_TwoPinsCategory)) when 1 then (dbo.fn_GetClassCodeToName(Pay_TwoPinsCategory, Pay_TwoPinsCategory)) when 2 then Pay_ReceivableProject end) as '项目名称' FROM tb_ErpPayment GO IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'Vw_StaffPerformance_Photograph') BEGIN DROP VIEW [dbo].Vw_StaffPerformance_Photograph END GO create View Vw_StaffPerformance_Photograph as SELECT Ordv_Number as 主订单 ,Ordv_ViceNumber as 副订单 ,(select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number) as 订单类型 ,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number) when 0 then (select stuff((select ','+ Ordpg_Sights from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,'')) when 1 then (select stuff((select ','+ Ordpg_Sights from tb_ErpOrdersPhotography where Ordv_ViceNumber=Ordpg_ViceNumber for xml path('')),1,1,'')) when 2 then (select stuff((select ','+ Ordpg_Sights from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,'')) else '' end) as 拍摄名称 ,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number) when 0 then (select max(Ordpg_PhotographyTime) from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number) when 1 then (select max(Ordpg_PhotographyTime) from tb_ErpOrdersPhotography where Ordv_ViceNumber=Ordpg_ViceNumber) when 2 then (select max(Ordpg_PhotographyTime) from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number) else '' end) as 最后拍摄时间 ,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number) when 0 then (select count(id) as id from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number and (Ordpg_PhotographyStatus=0 or Ordpg_PhotographyStatus=2)) when 1 then (select count(id) as id from tb_ErpOrdersPhotography where Ordv_ViceNumber=Ordpg_ViceNumber and (Ordpg_PhotographyStatus=0 or Ordpg_PhotographyStatus=2)) when 2 then (select count(id) as id from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number and (Ordpg_PhotographyStatus=0 or Ordpg_PhotographyStatus=2)) else '' end) as 未拍个数 ,(case Ordv_FilmSelectionStatus when 1 then 'OK' else '未选' end) as 选片状态 ,Ordv_FilmSelectionTime as 选片时间 ,(case when (select Count(*) from (select [OPlist_PickupStatus] from [tb_ErpOrderProductList] where [OPlist_ViceNumber]=Ordv_ViceNumber and OPlist_Type = '2' and [OPlist_PickupStatus] = '0') as ta)>0 then '未取' else 'OK' end) AS 取件状态 ,(select top 1 OPlist_PickupTime from tb_ErpOrderProductList where OPlist_ViceNumber = Ordv_ViceNumber and OPlist_Type = '2' and OPlist_PickupTime is not null order by OPlist_PickupTime) AS 取件日期 ,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number) when 0 then (select stuff((select ','+ Ordpg_Photographer from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,'')) when 1 then (select stuff((select ','+ Ordpg_Photographer from tb_ErpOrdersPhotography where Ordv_ViceNumber=Ordpg_ViceNumber for xml path('')),1,1,'')) when 2 then (select stuff((select ','+ Ordpg_Photographer from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,'')) else '' end) as 主摄影师ID ,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number) when 0 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_Photographer) from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,'')) when 1 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_Photographer) from tb_ErpOrdersPhotography where Ordv_ViceNumber=Ordpg_ViceNumber for xml path('')),1,1,'')) when 2 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_Photographer) from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,'')) else '' end) as 主摄影师名称 ,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number) when 0 then (select stuff((select ','+ Ordpg_PhotographyAssistant from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,'')) when 1 then (select stuff((select ','+ Ordpg_PhotographyAssistant from tb_ErpOrdersPhotography where Ordv_ViceNumber=Ordpg_ViceNumber for xml path('')),1,1,'')) when 2 then (select stuff((select ','+ Ordpg_PhotographyAssistant from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,'')) else '' end) as 摄影助理ID ,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number) when 0 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_PhotographyAssistant) from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,'')) when 1 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_PhotographyAssistant) from tb_ErpOrdersPhotography where Ordv_ViceNumber=Ordpg_ViceNumber for xml path('')),1,1,'')) when 2 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_PhotographyAssistant) from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,'')) else '' end) as 摄影助理名称 ,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number) when 0 then (select stuff((select ','+ Ordpg_MakeupArtist from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,'')) when 1 then (select stuff((select ','+ Ordpg_MakeupArtist from tb_ErpOrdersPhotography where Ordv_ViceNumber=Ordpg_ViceNumber for xml path('')),1,1,'')) when 2 then (select stuff((select ','+ Ordpg_MakeupArtist from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,'')) else '' end) as 主化妆ID ,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number) when 0 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_MakeupArtist) from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,'')) when 1 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_MakeupArtist) from tb_ErpOrdersPhotography where Ordv_ViceNumber=Ordpg_ViceNumber for xml path('')),1,1,'')) when 2 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_MakeupArtist) from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,'')) else '' end) as 主化妆名称 ,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number) when 0 then (select stuff((select ','+ Ordpg_MakeupAssistant from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,'')) when 1 then (select stuff((select ','+ Ordpg_MakeupAssistant from tb_ErpOrdersPhotography where Ordv_ViceNumber=Ordpg_ViceNumber for xml path('')),1,1,'')) when 2 then (select stuff((select ','+ Ordpg_MakeupAssistant from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,'')) else '' end) as 化妆助理ID ,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number) when 0 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_MakeupAssistant) from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,'')) when 1 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_MakeupAssistant) from tb_ErpOrdersPhotography where Ordv_ViceNumber=Ordpg_ViceNumber for xml path('')),1,1,'')) when 2 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_MakeupAssistant) from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,'')) else '' end) as 化妆助理名称 ,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number) when 0 then (select stuff((select ','+ Ordpg_BootDivision from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,'')) when 1 then (select stuff((select ','+ Ordpg_BootDivision from tb_ErpOrdersPhotography where Ordv_ViceNumber=Ordpg_ViceNumber for xml path('')),1,1,'')) when 2 then (select stuff((select ','+ Ordpg_BootDivision from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,'')) else '' end) as 引导师ID ,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number) when 0 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_BootDivision) from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,'')) when 1 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_BootDivision) from tb_ErpOrdersPhotography where Ordv_ViceNumber=Ordpg_ViceNumber for xml path('')),1,1,'')) when 2 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_BootDivision) from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,'')) else '' end) as 引导师名称 ,Vw_StaffPerformance_OrdersPerson.ID ,订单号 ,拍摄阶段 ,收款类别 ,二销类别编号 ,二销类别名称 ,收款金额 ,接单人编号 ,接单人名称 ,收款人编号 ,收款人名称 ,付款方式编号 ,付款方式名称 ,接单地点 ,收款项目 ,审核状态 ,审核人 ,备注 ,收款时间 ,收款类型 ,客户名称 ,套系类别 ,套系名称 ,应收金额 ,项目名称 FROM tb_ErpOrderDigital left join Vw_StaffPerformance_OrdersPerson on Ordv_Number=订单号 where 订单号 is not null GO IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'Vw_StaffPerformance_MemberCardRechargeRecord') BEGIN DROP VIEW [dbo].Vw_StaffPerformance_MemberCardRechargeRecord END GO create View Vw_StaffPerformance_MemberCardRechargeRecord as SELECT ID ,Mcrr_Number as 会员编号 ,Mcrr_RechargeNumber as 充值编号 ,Mcrr_RechargeName as 充值名称 ,Mcrr_RechargeAmount as 充值金额 ,Mcrr_DonateAmount as 赠送金额 ,Mcrr_DonateDiscount as 消费折扣 ,Mcrr_PaymentMethod as 支付方式编号 ,dbo.fn_GetClassCodeToName(Mcrr_PaymentMethod, Mcrr_PaymentMethod) as 支付方式名称 ,Mcrr_OrderPerson as 接单人编号 ,dbo.fn_CheckUserIDGetUserName(Mcrr_OrderPerson) AS 接单人名称 ,convert(varchar(10),Mcrr_CreateDatetime,120) as 创建时间 ,Mcrr_CreateName as 创建人编号 ,dbo.fn_CheckUserIDGetUserName(Mcrr_CreateName) AS 创建人名称 ,(select Cus_Name from tb_ErpCustomer where Cus_CustomerNumber=(select Mc_CustomerNumber from tb_ErpMemberCard where Mcrr_Number=Mc_Number)) as 客户姓名 FROM tb_ErpMemberCardRechargeRecord GO IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'Vw_ErpMemberCardPayment') BEGIN DROP VIEW [dbo].Vw_ErpMemberCardPayment END GO create View Vw_ErpMemberCardPayment as SELECT ID ,Mcpt_PaymentNumber as 收款编号 ,Mcpt_Number as 会员卡编号 ,Mcpt_PaymentType as 收款类型 ,Mcpt_PaymentAmount as 收款金额 ,Mcpt_PaymentMethod as 付款方式编号 ,dbo.fn_GetClassCodeToName(Mcpt_PaymentMethod, Mcpt_PaymentMethod) as 支付方式名称 ,Mcpt_PackageClass as 服务套系类别编号 ,(case Mcpt_PackageClass when '' then (select stuff((select ','+ Mcpcd_ServiceContent from tb_ErpMemberCardPaymentContentDetail where Mcpt_PaymentNumber=Mcpcd_PaymentNumber for xml path('')),1,1,'')) else (select MscPs_PackageName from tb_ErpMemberCardServicePackageSet where Mcpt_PackageClass=MscPs_PackageNumber) end) as 服务套系类别名称 ,Mcpt_PackagePrice as 服务套系价格 ,Mcpt_Discount as 优惠折扣 ,Mcpt_OrderPerson as 接单人编号 ,dbo.fn_CheckUserIDGetUserName(Mcpt_OrderPerson) AS 接单人名称 ,Mcpt_PaymentRemark as 收款备注 ,Mcpt_FinancialAudit 财务审核状态 ,Mcpt_FinancialAuditName as 账务审核人 ,Mcpt_FinancialAuditDateTime as 财务审核时间 ,Mcpt_FinancialAuditRemark as 账务审核备注 ,convert(varchar(10),Mcpt_CreateDatetime,120) as 创建日期 ,Mcpt_CreateName as 创建人编号 ,dbo.fn_CheckUserIDGetUserName(Mcpt_CreateName) AS 创建人名称 ,(select Cus_Name from tb_ErpCustomer where Cus_CustomerNumber=(select Mc_CustomerNumber from tb_ErpMemberCard where Mcpt_Number=Mc_Number)) as 客户姓名 FROM tb_ErpMemberCardPayment GO IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'Vw_ReportTime') BEGIN DROP VIEW [dbo].Vw_ReportTime END GO create View Vw_ReportTime as with t as( select distinct convert(varchar(10),isnull(Vw_OrdersWagePaymentRecords.Pay_CreateDatetime,View_ErpOtherIncomeAndExpenses.Oiae_IEDatetime),120) as Pay_CreateDatetimes ,Pay_OrdNumber,Pay_CreateDatetime,Oiae_ProjectName,Oiae_IEDatetime from Vw_OrdersWagePaymentRecords full join View_ErpOtherIncomeAndExpenses on Vw_OrdersWagePaymentRecords.Pay_CreateDatetime=View_ErpOtherIncomeAndExpenses.Oiae_IEDatetime ) , t2 as( select distinct convert(varchar(10),isnull(t.Pay_CreateDatetimes,Vw_ErpMemberCardPayment.创建日期),120) as Pay_CreateDatetimes from t full join Vw_ErpMemberCardPayment on t.Pay_CreateDatetimes=Vw_ErpMemberCardPayment.创建日期 ) select distinct convert(varchar(10),isnull(t2.Pay_CreateDatetimes,Vw_StaffPerformance_MemberCardRechargeRecord.创建时间),120) as Pay_CreateDatetimes from t2 full join Vw_StaffPerformance_MemberCardRechargeRecord on t2.Pay_CreateDatetimes=Vw_StaffPerformance_MemberCardRechargeRecord.创建时间 GO IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'Vw_MonthlyReport') BEGIN DROP VIEW [dbo].Vw_MonthlyReport END GO create View Vw_MonthlyReport as select Pay_CreateDatetimes ,(select sum(Pay_AmountOf) from tb_ErpPayment where (Pay_Category='全款' or Pay_Category='预约收款') and Pay_PaymentMethod!='BEBACCAFEGECFBJFD' and Pay_CreateDatetime>=Pay_CreateDatetimes and Pay_CreateDatetime<=Pay_CreateDatetimes+' 23:59:59.000') as DayOrdersIncome , (select sum(Pay_AmountOf) from tb_ErpPayment where Pay_Category='预约补款' and Pay_PaymentMethod!='BEBACCAFEGECFBJFD' and Pay_CreateDatetime>=Pay_CreateDatetimes and Pay_CreateDatetime<=Pay_CreateDatetimes+' 23:59:59.000') as DayReplenishmentIncome ,(select sum(Pay_AmountOf) from tb_ErpPayment where Pay_Category='后期收款' and Pay_PaymentMethod!='BEBACCAFEGECFBJFD' and Pay_CreateDatetime>=Pay_CreateDatetimes and Pay_CreateDatetime<=Pay_CreateDatetimes+' 23:59:59.000') as DayLateStageIncome , (select sum(Oiae_Money) from tb_ErpOtherIncomeAndExpenses where Oiae_Type='收入' and Oiae_PaymentMethod!='BEBACCAFEGECFBJFD' and Oiae_IEDatetime>=Pay_CreateDatetimes and Oiae_IEDatetime<=Pay_CreateDatetimes+' 23:59:59.000') as DayOtherIncome ,(select sum(Mcrr_RechargeAmount) from tb_ErpMemberCardRechargeRecord where Mcrr_CreateDatetime>=Pay_CreateDatetimes and Mcrr_CreateDatetime<=Pay_CreateDatetimes+' 23:59:59.000') as DayMemberIncome ,(select sum(Ord_SeriesPrice) from tb_ErpOrder where ord_class='1' and Ord_CreateDatetime>=Pay_CreateDatetimes and Ord_CreateDatetime<=Pay_CreateDatetimes+' 23:59:59.000') as DayEarlyPerformance ,(select sum(Plu_Amount) from tb_ErpPlusPickItems where Plu_CreateTime>=Pay_CreateDatetimes and Plu_CreateTime<=Pay_CreateDatetimes+' 23:59:59.000') as DayPluslatepickPerformance ,(select sum(Tsorder_Money) from tb_ErpTwoSalesOrder where Tsorder_CreateDatetime>=Pay_CreateDatetimes and Tsorder_CreateDatetime<=Pay_CreateDatetimes+' 23:59:59.000') as DayOtherPerformance , (select sum(Oiae_Money) from tb_ErpOtherIncomeAndExpenses where Oiae_Type='支出' and Oiae_IEDatetime>=Pay_CreateDatetimes and Oiae_IEDatetime<=Pay_CreateDatetimes+' 23:59:59.000') as DayExpenditure ,(select sum(Mcpt_PaymentAmount) from tb_ErpMemberCardPayment where Mcpt_CreateDatetime>=Pay_CreateDatetimes and Mcpt_CreateDatetime<=Pay_CreateDatetimes+' 23:59:59.000') as DayMemberCardPaymentIncome from ( select Pay_CreateDatetimes from Vw_ReportTime ) as MonthlyReport group by Pay_CreateDatetimes GO IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'Vw_YearReport') BEGIN DROP VIEW [dbo].Vw_YearReport END GO create View Vw_YearReport as select Pay_CreateDatetimes ,(select sum(Pay_AmountOf) from tb_ErpPayment where (Pay_Category='全款' or Pay_Category='预约收款') and Pay_PaymentMethod!='BEBACCAFEGECFBJFD' and convert(varchar,Pay_CreateDatetime,120) like '%'+Pay_CreateDatetimes+'%') as DayOrdersIncome , (select sum(Pay_AmountOf) from tb_ErpPayment where Pay_Category='预约补款' and Pay_PaymentMethod!='BEBACCAFEGECFBJFD' and convert(varchar,Pay_CreateDatetime,120) like '%'+Pay_CreateDatetimes+'%') as DayReplenishmentIncome ,(select sum(Pay_AmountOf) from tb_ErpPayment where Pay_Category='后期收款' and Pay_PaymentMethod!='BEBACCAFEGECFBJFD' and convert(varchar,Pay_CreateDatetime,120) like '%'+Pay_CreateDatetimes+'%') as DayLateStageIncome , (select sum(Oiae_Money) from tb_ErpOtherIncomeAndExpenses where Oiae_Type='收入' and Oiae_PaymentMethod!='BEBACCAFEGECFBJFD' and convert(varchar,Oiae_IEDatetime,120) like '%'+Pay_CreateDatetimes+'%') as DayOtherIncome ,(select sum(Mcrr_RechargeAmount) from tb_ErpMemberCardRechargeRecord where convert(varchar,Mcrr_CreateDatetime,120) like '%'+Pay_CreateDatetimes+'%') as DayMemberIncome ,(select sum(Ord_SeriesPrice) from tb_ErpOrder where ord_class='1' and convert(varchar,Ord_CreateDatetime,120) like '%'+Pay_CreateDatetimes+'%') as DayEarlyPerformance ,(select sum(Plu_Amount) from tb_ErpPlusPickItems where convert(varchar,Plu_CreateTime,120) like '%'+Pay_CreateDatetimes+'%') as DayPluslatepickPerformance ,(select sum(Tsorder_Money) from tb_ErpTwoSalesOrder where convert(varchar,Tsorder_CreateDatetime,120) like '%'+Pay_CreateDatetimes+'%') as DayOtherPerformance , (select sum(Oiae_Money) from tb_ErpOtherIncomeAndExpenses where Oiae_Type='支出' and convert(varchar,Oiae_IEDatetime,120) like '%'+Pay_CreateDatetimes+'%') as DayExpenditure ,(select sum(Mcpt_PaymentAmount) from tb_ErpMemberCardPayment where convert(varchar,Mcpt_CreateDatetime,120) like '%'+Pay_CreateDatetimes+'%') as DayMemberCardPaymentIncome from ( select convert(varchar(7),Pay_CreateDatetimes,120) as Pay_CreateDatetimes from Vw_ReportTime ) as YearReport group by Pay_CreateDatetimes GO IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'Vw_ProcessStatus') BEGIN DROP VIEW [dbo].Vw_ProcessStatus END GO create View [dbo].[Vw_ProcessStatus] as Select ID ,Ordv_Number as 主订单号 ,Ordv_ViceNumber as 副订单号 ,Ordv_DigitalNumber as 拍摄次数 ,(select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number) as 订单类型 ,(select Ord_Class from tb_ErpOrder where Ordv_Number=Ord_Number) as 订单类别 ,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number) when 1 then (select Ordpg_Sights from tb_ErpOrdersPhotography where Ordv_ViceNumber=Ordpg_ViceNumber) else (select stuff((select ','+ Ordpg_Sights from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,'')) end) as 拍摄名称 ,(select Ord_CreateDateTime from tb_ErpOrder where Ordv_Number=Ord_Number) as 预选时间 ,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number) when 0 then (select count(id) as id from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number and (Ordpg_PhotographyStatus=0 or Ordpg_PhotographyStatus=2)) when 1 then (select count(id) as id from tb_ErpOrdersPhotography where Ordv_ViceNumber=Ordpg_ViceNumber and (Ordpg_PhotographyStatus=0 or Ordpg_PhotographyStatus=2)) when 2 then (select count(id) as id from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number and (Ordpg_PhotographyStatus=0 or Ordpg_PhotographyStatus=2)) else '' end) as 未拍个数 , (case Ordv_EarlyRepairStatus when 2 then 'OK' else '未修'end ) as 初修状态 , (case Ordv_FilmSelectionStatus when 1 then 'OK' else '未选'end ) as 选片状态 , (case Ordv_RefinementStatus when 2 then 'OK' else '未修'end ) as 精修状态 , (case Ordv_DesignerStatus when 2 then 'OK' else '未设计'end ) as 设计状态 , (case Ordv_LookDesignStatus when 1 then 'OK' else '未看'end ) as 看设计状态 ,(select count(id) as id from tb_ErpOrderProductList where OPlist_Type=2 and OPlist_ViceNumber=Ordv_ViceNumber and OPlist_SendStatus=0) as 未发出个数 ,(select count(id) as id from tb_ErpOrderProductList where OPlist_Type=2 and OPlist_ViceNumber=Ordv_ViceNumber and OPlist_CompletedStatus=0) as 未完成个数 ,(case when (select Count(*) from (select [OPlist_PickupStatus] from [tb_ErpOrderProductList] where [OPlist_ViceNumber]=Ordv_ViceNumber and OPlist_Type = '2' and [OPlist_PickupStatus] = '0') as ta)>0 then '未取' else 'OK' end) AS 取件状态 from tb_ErpOrderDigital GO IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'Vw_ErpCustomersTrackRecord') BEGIN DROP VIEW [dbo].Vw_ErpCustomersTrackRecord END GO create View Vw_ErpCustomersTrackRecord as SELECT id ,TR_CustomerGroupID as 客户组 ,TR_CustomerID as 客户ID ,(select Cus_Name from tb_ErpCustomer where TR_CustomerID=Cus_CustomerNumber) as 客户姓名 ,TR_TraceWay as 沟通方式 ,TR_TraceType as 沟通类型 ,TR_SpecificMatters ,TR_Communicationstatus as 沟通状态 ,TR_TraceDetailedly ,TR_CallRecording as 录音路径文件 ,TR_Remark as 备注 ,TR_TraceDateTime as 沟通日期 ,TR_TraceTimeLength as 沟通时长 ,TR_TracePersonID as 跟踪人员ID ,(select User_Name from tb_ErpUser where TR_TracePersonID=User_EmployeeID) as 跟踪人员姓名 ,TR_CreateDateTime as 创建时间 FROM dbo.tb_ErpCustomersTrackRecord GO